-- 1.
-- 说明：题目为连续值的求解类型
-- 思路：
-- a、使用 row_number 在组内给数据编号(rownum)
-- b、某个值 - rownum = gid，得到结果可以作为后面分组计算的依据
-- c、根据求得的gid，作为分组条件，求最终结果
with tmp as (
select h1.team, h1.year, 
    h1.year - row_number() over(partition by h1.team order by h1.year) diff
    from h1 
)
select team, count(*) num
    from tmp
    group by team, diff
    having num >= 3;
    
    

-- 2.
-- 说明：波峰 - 大于前后的列； 波谷 - 小于前后的列
-- 所以要使用到序列函数 lag:返回当前数据行的上一行数据; lead：返回当前数据行的下一行数据
with tmp as (
select id, time, price,  
    price - lag(price, 1, price) over(partition by id order by time) p1,
    price - lead(price, 1, price) over(partition by id order by time) p2
from h2
)
select id, time, price, 
    
    case when p1 > 0 and p2 > 0 then '波峰' 
         when p1 < 0 and p1 < 0 then '波谷'
    end feature
from tmp
where (p1 > 0 and p2 > 0) or (p1 < 0 and p2 < 0);



-- 3.1
-- 说明：根据ID分组， 浏览时时长为：同一个组内的最大值和最小值的差(换算成时间戳以分钟为单位显示)；步长：分组内的总数
select id, ( max(unix_timestamp(dt, 'yyyy/MM/dd HH:mm')) - min(unix_timestamp(dt, 'yyyy/MM/dd HH:mm')) ) / 60 duration, count(*) step
    from h3
group by id;


-- 3.2
-- 说明：
-- 根据id分区， 用时间dt排序，计算分组内相邻两行直接的时间差，超过30分钟为一类，小于30分钟为一类
-- sum(group_field) over(partition by id order by dt) group_fields : order by子句；sum函数 从分组的第一行到当前行求和，这样
-- 第一次浏览，第二次浏览，第三次浏览 计算出来的group_fields 为 0， 1， 2， 后面则用group_fields进行分组
with tmp as (
    select id, dt,
            case when (UNIX_TIMESTAMP(dt,"yyyy/MM/dd HH:mm")-UNIX_TIMESTAMP(lag(dt,1,dt) over(partition by id order by dt) ,"yyyy/MM/dd HH:mm"))/60 > 30 then 0
                 else (UNIX_TIMESTAMP(dt,"yyyy/MM/dd HH:mm")-UNIX_TIMESTAMP(lag(dt,1,dt) over(partition by id order by dt) ,"yyyy/MM/dd HH:mm"))/60
            end duration_tmp,
            case when (UNIX_TIMESTAMP(dt,"yyyy/MM/dd HH:mm")-UNIX_TIMESTAMP(lag(dt,1,dt) over(partition by id order by dt) ,"yyyy/MM/dd HH:mm"))/60 > 30 then 1
                else 0
            end group_field
    from h3
),
tmp2 as (
    select id, dt, group_field, sum(group_field) over(partition by id order by dt) group_fields, duration_tmp 
            from tmp    
)
select id, sum(duration_tmp) duration, count(*) step 
    from tmp2
group by id, group_fields;
